# -*- encoding=utf-8 -*-

import pymysql
import configparser
import datetime
from app import app
from model.feedback import Feedback

class DBManager(object):
    def __init__(self):
        configPath = app.root_path + "/ym.conf"
        self.config = configparser.ConfigParser()
        self.config.read(configPath)
        self.host = self.config.get("mysql", "host")
        self.port = int(self.config.get("mysql", "port"))
        self.schema = self.config.get("mysql", "schema")
        self.username = self.config.get("mysql", "username")
        self.password = self.config.get("mysql", "password")
        self.conn = pymysql.connect(self.host, self.username, self.password, self.schema,port=self.port, charset='utf8')

    def executeSql(self, sql):
        cursor = self.conn.cursor()
        cursor.execute(sql)
        return cursor

    def insertToFeedback(self, name=None, phone=None, address=None,\
                         booktime=None,serviceType=None, title=None, content=None, operator=None, operatorId=None, status=0):
        sql = "INSERT INTO `feedback` ("
        values = "VALUES ("

        if name is not None:
            sql = sql + "`name`,"
            values += "'%s'," %name
        if phone is not None:
            sql = sql + "`phone`,"
            values += "'%s'," %phone
        if address is not None:
            sql = sql + "`address`,"
            values += "'%s'," %address
        if booktime is not None:
            sql = sql + "`booktime`,"
            values += "'%s'," %booktime
        if serviceType is not None:
            sql = sql + "`service_type`,"
            values += "'%s'," %serviceType
        if title is not None:
            sql = sql + "`title`,"
            values += "'%s'," % title
        if content is not None:
            sql = sql + "`content`,"
            values += "'%s'," %content
        if operator is not None:
            sql = sql + "`operator`,"
            values += "'%s'," %operator
        if operatorId is not None:
            sql = sql + "`operator_id`,"
            values += "%s," % operatorId
        if status is not None:
            sql = sql + "`status`,"
            values += "%s," % status

        sql = sql + "`create_time`"
        now_time = datetime.datetime.now()
        values += "%s" % int(now_time.timestamp())

        sql = sql + ") "
        values = values + ")"
        sql = sql + values
        print(sql)
        cursor = self.conn.cursor()
        cursor.execute(sql)
        self.conn.commit()

        query_sql = "select id from feedback where create_time = %s and name = '%s'" %(int(now_time.timestamp()), name)
        cursor.execute(query_sql)
        row = cursor.fetchone()
        if row is not None:
            return row[0]
        return -1


    def selectAllFeedbacks(self):
        sql = "select * from feedback where status != 3 order by create_time desc"
        cursor = self.conn.cursor()
        cursor.execute(sql)
        feedbacks = []
        row = cursor.fetchone()
        while row is not None:
            fb = self.transToFeedBack(row)
            feedbacks.append(fb)
            row = cursor.fetchone()
        return feedbacks

    def select_feedbacks_by_status(self, status):
        sql = "select * from feedback where status = %s order by create_time desc" %status
        cursor = self.conn.cursor()
        cursor.execute(sql)
        feedbacks = []
        row = cursor.fetchone()
        while(row is not None):
            fb = self.transToFeedBack(row)
            feedbacks.append(fb)
            row = cursor.fetchone()
        return feedbacks

    def selectFeedBackById(self,id):

        sql = "select * from feedback where id = %s" % id
        cursor = self.conn.cursor()
        cursor.execute(sql)
        row = cursor.fetchone()
        if row is None:
            return None
        fb = self.transToFeedBack(row)
        return fb

    def process_one_item_by_id(self,id,operator_id):
        self.change_status_for_one_item(id, operator_id, 1)

    def end_one_item_by_id(self, id, operator_id):
        self.change_status_for_one_item(id, operator_id, 2)

    def delete_one_item_by_id(self, id, operator_id):
        self.change_status_for_one_item(id, operator_id, 3)

    def revive_one_item_by_id(self, id, operator_id):
        self.change_status_for_one_item(id, operator_id, 0)

    def get_name_by_operator_id(self, operator_id):
        cursor = self.conn.cursor()
        sql = "select name from yimu_user where username = '%s'" % operator_id
        cursor.execute(sql)
        row = cursor.fetchone()
        name = row[0]
        return name

    def change_status_for_one_item(self, id, operator_id, status):
        cursor = self.conn.cursor()
        name = self.get_name_by_operator_id(operator_id)
        sql = "update `feedback` set `status` = %s ,`operator` = '%s' , `operator_id` = '%s' where id = %s" \
              % (status, name, operator_id, id)
        cursor.execute(sql)
        self.conn.commit()

    def transToFeedBack(self, row):
        fb = Feedback(id=row[0],name=row[1],phone=row[2],address=row[3],booktime=row[4],serviceType=row[5],\
                      content=row[6],operator=row[7],operatorId=row[8],status=row[9],title=row[10],createTime=row[11])
        return fb

    def checkUser(self, username, password):
        if username is None or password is None:
            return False
        if username.strip(' ') == '':
            return False
        if password.strip(' ') == '':
            return False
        sql = "select password from yimu_user where `username` = '%s'" %username
        cursor = self.conn.cursor()
        cursor.execute(sql)
        row = cursor.fetchone()
        if row is None:
            return False
        if row[0] != password:
            return False
        return True

    def get_username_by_wechat_id(self,wechat_id):
        sql = "select username from yimu_user where `wechat_id` = '%s'" %wechat_id
        cursor = self.conn.cursor()
        cursor.execute(sql)
        row = cursor.fetchone()
        if row is None:
            return None
        return row[0]

    def add_user(self, username, password, password_repeat, name, wechat_id):
        if self.is_blank(username):
            return "用户名为空"
        if self.is_blank(password):
            return "密码为空"
        if password != password_repeat:
            return "两次输入的密码不一致"
        sql = "insert into yimu_user(`username`,`password`,`name`,`wechat_id`) values('%s','%s','%s','%s')"\
                %(username, password, name, wechat_id)
        cursor = self.conn.cursor()
        cursor.execute(sql)
        self.conn.commit()
        return "success"

    def get_last_id_in_PGD(self):
        sql = "select last_id from last_PGD_id"
        cursor = self.conn.cursor()
        cursor.execute(sql)
        row = cursor.fetchone()
        if row is None:
            self.init_PGD_table()
            return -1
        return row[0]

    def init_PGD_table(self):
        sql = "insert into last_PGD_id(`last_id`) values(-1)"
        cursor = self.conn.cursor()
        cursor.execute(sql)
        self.conn.commit()

    def update_last_id(self, id):
        date = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        sql = "update last_PGD_id set last_id = %s, date = '%s'" %(id, date)
        cursor = self.conn.cursor()
        cursor.execute(sql)
        self.conn.commit()


    def close(self):
        self.conn.close()

    def is_blank(self, s):
        s = s.strip(' ')
        s = s.strip('\t')
        s = s.strip('\n')
        s = s.strip('\r')
        if s == '':
            return True
        return False